CREATE PROCEDURE [dbo].[asi_GetBlogArchiveMonths]
    @parentHierarchyKey uniqueidentifier,
    @userKey uniqueidentifier,
    @loggedInUserGroupKey uniqueidentifier = '00000000-0000-0000-0000-000000000000' -- if this is empty, we assume the user is not logged in
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @publishedOnly bit
    DECLARE @ignoreLicensing bit

    SET @publishedOnly = 1
    SET @ignoreLicensing = 0;

    SELECT TOP 0 a.HierarchyKey, b.DocumentKey, b.DocumentVersionKey, b.DocumentStatusCode,
              b.DocumentName, b.AlternateName, b.DocumentDescription, b.DocumentTypeCode,
              b.IsSystem, b.ContainsChildrenFlag, b.RelatedDocumentVersionKey, b.AccessKey,
              b.DefaultChildAccessKey, b.StatusUpdatedOn, b.StatusUpdatedByUserKey, b.CreatedOn,
              b.CreatedByUserKey, b.UpdatedOn, b.UpdatedByUserKey, c.DocumentTypeName, c.DocumentTypeDesc,
              c.DocumentIconURL, d.DocumentKey AS RelatedDocumentKey, d.DocumentTypeCode AS RelatedDocumentTypeCode,
              d.DocumentName AS RelatedDocumentName, d.AlternateName AS RelatedAlternateName,
              d.DocumentDescription AS RelatedDocumentDescription, e.DocumentTypeName AS RelatedDocumentTypeName,
              e.DocumentTypeDesc AS RelatedDocumentTypeDesc, e.DocumentIconURL AS RelatedDocumentIconURL,
              e.ShortcutIconURL AS RelatedShortcutIconURL
         INTO #ChildDocuments
         FROM [dbo].[Hierarchy] a INNER JOIN [dbo].[DocumentMain] b ON a.UniformKey = b.DocumentVersionKey
          INNER JOIN [dbo].[DocumentTypeRef] c ON b.DocumentTypeCode = c.DocumentTypeCode
              LEFT OUTER JOIN [dbo].[DocumentMain] d ON b.RelatedDocumentVersionKey = d.DocumentVersionKey
              LEFT OUTER JOIN [dbo].[DocumentTypeRef] e ON d.DocumentTypeCode = e.DocumentTypeCode

    INSERT INTO #ChildDocuments
    EXECUTE [dbo].[asi_DocumentMainListByParentHierarchyKey] @parentHierarchyKey, @userKey, @loggedInUserGroupKey, @publishedOnly, @ignoreLicensing

    SELECT RIGHT('0' + RTRIM(MONTH(d.CreatedOn)),2) + '/' + RTRIM(YEAR(d.CreatedOn)) AS ArchiveDate
    FROM #ChildDocuments cd
        INNER JOIN Hierarchy h ON cd.HierarchyKey = h.ParentHierarchyKey
        INNER JOIN DocumentMain d ON h.UniformKey = d.DocumentVersionKey AND d.DocumentTypeCode = 'CTY'
    GROUP BY RIGHT('0' + RTRIM(MONTH(d.CreatedOn)),2) + '/' + RTRIM(YEAR(d.CreatedOn))

    DROP TABLE #ChildDocuments
END


GO